﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_FindDoc]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_FindDoc];
GO
CREATE PROCEDURE [dbo].[sproc_FindDoc]
    @UserName nvarchar(300),
    @KeyString nvarchar(300),
    @SearchRange int,
    @ViewDocRightCode int = 10,
    @Inherit bit =0
AS
SET NOCOUNT ON


CREATE TABLE #ct(classid int)
INSERT INTO #ct (classid) EXEC sproc_GetAllClassToAccess @UserName,@ViewDocRightCode,@Inherit

CREATE TABLE #TD (
    [DocID] [int]  NOT NULL
) 
    --查找文档标题
    if @SearchRange&1=1
    INSERT INTO #TD
    SELECT DocID 
    FROM uds_document
    WHERE classid in (select * from #ct)
        and doctitle like '%' + @KeyString + '%'
    --查找文档内容
    if @SearchRange &2 = 2
    INSERT INTO #TD
    SELECT DocID 
    FROM uds_document
    WHERE classid in (select * from #ct)
        and doccontent like '%' + @KeyString + '%'
    --查找文档作者
    if @SearchRange &4 = 4
    INSERT INTO #TD
    SELECT DocID 
    FROM uds_document
    WHERE classid in (select * from #ct)
        and docAddedby like '%' + @KeyString + '%'
    --查找文档附件
    if @SearchRange &8 = 8
    BEGIN
        INSERT INTO #TD
        SELECT DocID 
        FROM uds_document
        WHERE classid in (select * from #ct)
            and docid in (SELECT a.docid from uds_files a,uds_scope b where b.path = a.filevisualpath and b.spid = @@spid)
        DELETE FROM uds_scope 
            WHERE spid = @@spid
    END

    --得出结果
    SELECT * FROM uds_document
        WHERE docid in (SELECT docid FROM #TD)

DROP TABLE #ct
DROP TABLE #TD
SET NOCOUNT OFF